# libs
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import os
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import LabelEncoder
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, confusion_matrix, accuracy_score, precision_score, recall_score, f1_score

# Caminho dos Arquivos
path = 'dataset/'

# Carreguei todos os aquivos um um dicionario de df
arquivos = [a for a in os.listdir(path) if a.endswith('.csv')]
dataframes = {arq.replace('.csv', ''): pd.read_csv(os.path.join(path, arq)) for arq in arquivos}

# Visualizando os df
dataframes['olist_customers_dataset']

# Verificando nulos
for tabela, df in dataframes.items():
    print(f"Nulos na tabela: {tabela}")
    print(df.isnull().sum())
    print("---------------" * 10)

Nulos na tabela: olist_order_payments_dataset
order_id                0
payment_sequential      0
payment_type            0
payment_installments    0
payment_value           0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_order_items_dataset
order_id               0
order_item_id          0
product_id             0
seller_id              0
shipping_limit_date    0
price                  0
freight_value          0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_orders_dataset
order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                 160
order_delivered_carrier_date     1783
order_delivered_customer_date    2965
order_estimated_delivery_date       0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: product_category_name_translation
product_category_name            0
product_category_name_english    0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_products_dataset
product_id                      0
product_category_name         610
product_name_lenght           610
product_description_lenght    610
product_photos_qty            610
product_weight_g                2
product_length_cm               2
product_height_cm               2
product_width_cm                2
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_sellers_dataset
seller_id                 0
seller_zip_code_prefix    0
seller_city               0
seller_state              0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_order_reviews_dataset
review_id                      0
order_id                       0
review_score                   0
review_comment_title       87656
review_comment_message     58247
review_creation_date           0
review_answer_timestamp        0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_geolocation_dataset
geolocation_zip_code_prefix    0
geolocation_lat                0
geolocation_lng                0
geolocation_city               0
geolocation_state              0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_customers_dataset
customer_id                 0
customer_unique_id          0
customer_zip_code_prefix    0
customer_city               0
customer_state              0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------

dataframes['olist_orders_dataset']['order_approved_at']

0        2017-10-02 11:07:15
1        2018-07-26 03:24:27
2        2018-08-08 08:55:23
3        2017-11-18 19:45:59
4        2018-02-13 22:20:29
                ...         
99436    2017-03-09 09:54:05
99437    2018-02-06 13:10:37
99438    2017-08-27 15:04:16
99439    2018-01-08 21:36:21
99440    2018-03-09 11:20:28
Name: order_approved_at, Length: 99441, dtype: object

dataframes['olist_orders_dataset']['order_delivered_carrier_date']

0        2017-10-04 19:55:00
1        2018-07-26 14:31:00
2        2018-08-08 13:50:00
3        2017-11-22 13:39:59
4        2018-02-14 19:46:34
                ...         
99436    2017-03-10 11:18:03
99437    2018-02-07 23:22:42
99438    2017-08-28 20:52:26
99439    2018-01-12 15:35:03
99440    2018-03-09 22:11:59
Name: order_delivered_carrier_date, Length: 99441, dtype: object

dataframes['olist_orders_dataset']['order_delivered_customer_date']

0        2017-10-10 21:25:13
1        2018-08-07 15:27:45
2        2018-08-17 18:06:29
3        2017-12-02 00:28:42
4        2018-02-16 18:17:02
                ...         
99436    2017-03-17 15:08:01
99437    2018-02-28 17:37:56
99438    2017-09-21 11:24:17
99439    2018-01-25 23:32:54
99440    2018-03-16 13:08:30
Name: order_delivered_customer_date, Length: 99441, dtype: object

# Excluir dados da tabela order_approved_at
dataframes['olist_orders_dataset'] = dataframes['olist_orders_dataset'].dropna(subset=['order_approved_at'])

dataframes['olist_orders_dataset']['order_approved_at'].isnull().sum()

np.int64(0)

# Object para datetime
dataframes['olist_orders_dataset']['order_approved_at'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_approved_at'], errors='coerce')
dataframes['olist_orders_dataset']['order_delivered_carrier_date'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_delivered_carrier_date'], errors='coerce')
dataframes['olist_orders_dataset']['order_delivered_customer_date'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_delivered_customer_date'], errors='coerce')

# Criacao de novas colunas
dataframes['olist_orders_dataset']['enviados'] = dataframes['olist_orders_dataset']['order_delivered_carrier_date'].notna().astype(int)
dataframes['olist_orders_dataset']['entregues'] = dataframes['olist_orders_dataset']['order_delivered_customer_date'].notna().astype(int)

dataframes['olist_orders_dataset']['entregues'].value_counts()

entregues
1    96462
0     2819
Name: count, dtype: int64

dataframes['olist_orders_dataset']['enviados'].value_counts()

enviados
1    97644
0     1637
Name: count, dtype: int64

dataframes['olist_products_dataset'] = dataframes['olist_products_dataset'].dropna(
    subset=['product_category_name', 'product_name_lenght', 'product_description_lenght', 'product_photos_qty'])

dataframes['olist_products_dataset'].isnull().sum()

product_id                    0
product_category_name         0
product_name_lenght           0
product_description_lenght    0
product_photos_qty            0
product_weight_g              1
product_length_cm             1
product_height_cm             1
product_width_cm              1
dtype: int64

dataframes['olist_products_dataset'].dropna(inplace=True)

dataframes['olist_products_dataset'].isnull().sum()

product_id                    0
product_category_name         0
product_name_lenght           0
product_description_lenght    0
product_photos_qty            0
product_weight_g              0
product_length_cm             0
product_height_cm             0
product_width_cm              0
dtype: int64

dataframes['olist_order_reviews_dataset']

# Verificando duplicatas
for tabela, df in dataframes.items():
    print(f"Duplicatas na tabela: {tabela}")
    print(df.duplicated().sum())
    print("---------------" * 10)

Duplicatas na tabela: olist_order_payments_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_order_items_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_orders_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: product_category_name_translation
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_products_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_sellers_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_order_reviews_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_geolocation_dataset
261831
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_customers_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------

# Ordenarei as duplicatas para ficarem juntas
geoloc = dataframes['olist_geolocation_dataset'].sort_values(by=dataframes['olist_geolocation_dataset'].columns.tolist())

# Mostrar apenas duplicatas ordenadas
geoloc[geoloc.duplicated(keep=False)]

# Excluindo duplicatas
dataframes['olist_geolocation_dataset'] = dataframes['olist_geolocation_dataset'].drop_duplicates()

# agora temos os dados limpos de duplicatas.
dataframes['olist_geolocation_dataset'].duplicated().sum()

np.int64(0)

for tabela, df in dataframes.items():
    print(f'Nome da tabela: {tabela}')
    print(df.info())
    print('-----' * 10)

Nome da tabela: olist_order_payments_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 103886 entries, 0 to 103885
Data columns (total 5 columns):
 #   Column                Non-Null Count   Dtype  
---  ------                --------------   -----  
 0   order_id              103886 non-null  object 
 1   payment_sequential    103886 non-null  int64  
 2   payment_type          103886 non-null  object 
 3   payment_installments  103886 non-null  int64  
 4   payment_value         103886 non-null  float64
dtypes: float64(1), int64(2), object(2)
memory usage: 4.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_order_items_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 112650 entries, 0 to 112649
Data columns (total 7 columns):
 #   Column               Non-Null Count   Dtype  
---  ------               --------------   -----  
 0   order_id             112650 non-null  object 
 1   order_item_id        112650 non-null  int64  
 2   product_id           112650 non-null  object 
 3   seller_id            112650 non-null  object 
 4   shipping_limit_date  112650 non-null  object 
 5   price                112650 non-null  float64
 6   freight_value        112650 non-null  float64
dtypes: float64(2), int64(1), object(4)
memory usage: 6.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_orders_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 99281 entries, 0 to 99440
Data columns (total 10 columns):
 #   Column                         Non-Null Count  Dtype         
---  ------                         --------------  -----         
 0   order_id                       99281 non-null  object        
 1   customer_id                    99281 non-null  object        
 2   order_status                   99281 non-null  object        
 3   order_purchase_timestamp       99281 non-null  object        
 4   order_approved_at              99281 non-null  datetime64[ns]
 5   order_delivered_carrier_date   97644 non-null  datetime64[ns]
 6   order_delivered_customer_date  96462 non-null  datetime64[ns]
 7   order_estimated_delivery_date  99281 non-null  object        
 8   enviados                       99281 non-null  int64         
 9   entregues                      99281 non-null  int64         
dtypes: datetime64[ns](3), int64(2), object(5)
memory usage: 8.3+ MB
None
--------------------------------------------------
Nome da tabela: product_category_name_translation
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 71 entries, 0 to 70
Data columns (total 2 columns):
 #   Column                         Non-Null Count  Dtype 
---  ------                         --------------  ----- 
 0   product_category_name          71 non-null     object
 1   product_category_name_english  71 non-null     object
dtypes: object(2)
memory usage: 1.2+ KB
None
--------------------------------------------------
Nome da tabela: olist_products_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 32340 entries, 0 to 32950
Data columns (total 9 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   product_id                  32340 non-null  object 
 1   product_category_name       32340 non-null  object 
 2   product_name_lenght         32340 non-null  float64
 3   product_description_lenght  32340 non-null  float64
 4   product_photos_qty          32340 non-null  float64
 5   product_weight_g            32340 non-null  float64
 6   product_length_cm           32340 non-null  float64
 7   product_height_cm           32340 non-null  float64
 8   product_width_cm            32340 non-null  float64
dtypes: float64(7), object(2)
memory usage: 2.5+ MB
None
--------------------------------------------------
Nome da tabela: olist_sellers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3095 entries, 0 to 3094
Data columns (total 4 columns):
 #   Column                  Non-Null Count  Dtype 
---  ------                  --------------  ----- 
 0   seller_id               3095 non-null   object
 1   seller_zip_code_prefix  3095 non-null   int64 
 2   seller_city             3095 non-null   object
 3   seller_state            3095 non-null   object
dtypes: int64(1), object(3)
memory usage: 96.8+ KB
None
--------------------------------------------------
Nome da tabela: olist_order_reviews_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99224 entries, 0 to 99223
Data columns (total 7 columns):
 #   Column                   Non-Null Count  Dtype 
---  ------                   --------------  ----- 
 0   review_id                99224 non-null  object
 1   order_id                 99224 non-null  object
 2   review_score             99224 non-null  int64 
 3   review_comment_title     11568 non-null  object
 4   review_comment_message   40977 non-null  object
 5   review_creation_date     99224 non-null  object
 6   review_answer_timestamp  99224 non-null  object
dtypes: int64(1), object(6)
memory usage: 5.3+ MB
None
--------------------------------------------------
Nome da tabela: olist_geolocation_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 738332 entries, 0 to 1000161
Data columns (total 5 columns):
 #   Column                       Non-Null Count   Dtype  
---  ------                       --------------   -----  
 0   geolocation_zip_code_prefix  738332 non-null  int64  
 1   geolocation_lat              738332 non-null  float64
 2   geolocation_lng              738332 non-null  float64
 3   geolocation_city             738332 non-null  object 
 4   geolocation_state            738332 non-null  object 
dtypes: float64(2), int64(1), object(2)
memory usage: 33.8+ MB
None
--------------------------------------------------
Nome da tabela: olist_customers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99441 entries, 0 to 99440
Data columns (total 5 columns):
 #   Column                    Non-Null Count  Dtype 
---  ------                    --------------  ----- 
 0   customer_id               99441 non-null  object
 1   customer_unique_id        99441 non-null  object
 2   customer_zip_code_prefix  99441 non-null  int64 
 3   customer_city             99441 non-null  object
 4   customer_state            99441 non-null  object
dtypes: int64(1), object(4)
memory usage: 3.8+ MB
None
--------------------------------------------------

# Os tipos dos dados nas tabelas estão normalizados
dataframes['olist_order_payments_dataset'].head()

dataframes['olist_order_items_dataset'].head()

# temos a coluna shipping_limit_date como object, transformarei a mesma em datetime
dataframes['olist_order_items_dataset']['shipping_limit_date'] = pd.to_datetime(dataframes['olist_order_items_dataset']['shipping_limit_date'], errors='coerce')

# Faltam 2 colunas serem mudadas para datetime, order_purchase_timestamp e order_estimated_delivery_date
dataframes['olist_orders_dataset'].head()

# Transformando as colunas object em datetime
dataframes['olist_orders_dataset']['order_purchase_timestamp'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_purchase_timestamp'], errors='coerce')
dataframes['olist_orders_dataset']['order_estimated_delivery_date'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_estimated_delivery_date'], errors='coerce')

# Os dados estão normalizados na tabela
dataframes['product_category_name_translation'].head()

# Os dados estão normalizados
dataframes['olist_products_dataset'].head()

# Os dados estão normalizados
dataframes['olist_sellers_dataset'].head()

# Temos as 2 colunas de data no formato object que irei transforma em datetime
dataframes['olist_order_reviews_dataset'].head()

# Tranformando em datetime
dataframes['olist_order_reviews_dataset']['review_creation_date'] = pd.to_datetime(dataframes['olist_order_reviews_dataset']['review_creation_date'], errors='coerce')
dataframes['olist_order_reviews_dataset']['review_answer_timestamp'] = pd.to_datetime(dataframes['olist_order_reviews_dataset']['review_answer_timestamp'], errors='coerce')

# Os dados estão normalizados
dataframes['olist_geolocation_dataset'].head()

# Os dados estão normalizados
dataframes['olist_customers_dataset'].head()

# Rodarei novamente o loop, para ficar visivel que todas as mudanças foram feitas
for tabela, df in dataframes.items():
    print(f'Nome da tabela: {tabela}')
    print(df.info())
    print('-----' * 10)

Nome da tabela: olist_order_payments_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 103886 entries, 0 to 103885
Data columns (total 5 columns):
 #   Column                Non-Null Count   Dtype  
---  ------                --------------   -----  
 0   order_id              103886 non-null  object 
 1   payment_sequential    103886 non-null  int64  
 2   payment_type          103886 non-null  object 
 3   payment_installments  103886 non-null  int64  
 4   payment_value         103886 non-null  float64
dtypes: float64(1), int64(2), object(2)
memory usage: 4.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_order_items_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 112650 entries, 0 to 112649
Data columns (total 7 columns):
 #   Column               Non-Null Count   Dtype         
---  ------               --------------   -----         
 0   order_id             112650 non-null  object        
 1   order_item_id        112650 non-null  int64         
 2   product_id           112650 non-null  object        
 3   seller_id            112650 non-null  object        
 4   shipping_limit_date  112650 non-null  datetime64[ns]
 5   price                112650 non-null  float64       
 6   freight_value        112650 non-null  float64       
dtypes: datetime64[ns](1), float64(2), int64(1), object(3)
memory usage: 6.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_orders_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 99281 entries, 0 to 99440
Data columns (total 10 columns):
 #   Column                         Non-Null Count  Dtype         
---  ------                         --------------  -----         
 0   order_id                       99281 non-null  object        
 1   customer_id                    99281 non-null  object        
 2   order_status                   99281 non-null  object        
 3   order_purchase_timestamp       99281 non-null  datetime64[ns]
 4   order_approved_at              99281 non-null  datetime64[ns]
 5   order_delivered_carrier_date   97644 non-null  datetime64[ns]
 6   order_delivered_customer_date  96462 non-null  datetime64[ns]
 7   order_estimated_delivery_date  99281 non-null  datetime64[ns]
 8   enviados                       99281 non-null  int64         
 9   entregues                      99281 non-null  int64         
dtypes: datetime64[ns](5), int64(2), object(3)
memory usage: 8.3+ MB
None
--------------------------------------------------
Nome da tabela: product_category_name_translation
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 71 entries, 0 to 70
Data columns (total 2 columns):
 #   Column                         Non-Null Count  Dtype 
---  ------                         --------------  ----- 
 0   product_category_name          71 non-null     object
 1   product_category_name_english  71 non-null     object
dtypes: object(2)
memory usage: 1.2+ KB
None
--------------------------------------------------
Nome da tabela: olist_products_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 32340 entries, 0 to 32950
Data columns (total 9 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   product_id                  32340 non-null  object 
 1   product_category_name       32340 non-null  object 
 2   product_name_lenght         32340 non-null  float64
 3   product_description_lenght  32340 non-null  float64
 4   product_photos_qty          32340 non-null  float64
 5   product_weight_g            32340 non-null  float64
 6   product_length_cm           32340 non-null  float64
 7   product_height_cm           32340 non-null  float64
 8   product_width_cm            32340 non-null  float64
dtypes: float64(7), object(2)
memory usage: 2.5+ MB
None
--------------------------------------------------
Nome da tabela: olist_sellers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3095 entries, 0 to 3094
Data columns (total 4 columns):
 #   Column                  Non-Null Count  Dtype 
---  ------                  --------------  ----- 
 0   seller_id               3095 non-null   object
 1   seller_zip_code_prefix  3095 non-null   int64 
 2   seller_city             3095 non-null   object
 3   seller_state            3095 non-null   object
dtypes: int64(1), object(3)
memory usage: 96.8+ KB
None
--------------------------------------------------
Nome da tabela: olist_order_reviews_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99224 entries, 0 to 99223
Data columns (total 7 columns):
 #   Column                   Non-Null Count  Dtype         
---  ------                   --------------  -----         
 0   review_id                99224 non-null  object        
 1   order_id                 99224 non-null  object        
 2   review_score             99224 non-null  int64         
 3   review_comment_title     11568 non-null  object        
 4   review_comment_message   40977 non-null  object        
 5   review_creation_date     99224 non-null  datetime64[ns]
 6   review_answer_timestamp  99224 non-null  datetime64[ns]
dtypes: datetime64[ns](2), int64(1), object(4)
memory usage: 5.3+ MB
None
--------------------------------------------------
Nome da tabela: olist_geolocation_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 738332 entries, 0 to 1000161
Data columns (total 5 columns):
 #   Column                       Non-Null Count   Dtype  
---  ------                       --------------   -----  
 0   geolocation_zip_code_prefix  738332 non-null  int64  
 1   geolocation_lat              738332 non-null  float64
 2   geolocation_lng              738332 non-null  float64
 3   geolocation_city             738332 non-null  object 
 4   geolocation_state            738332 non-null  object 
dtypes: float64(2), int64(1), object(2)
memory usage: 33.8+ MB
None
--------------------------------------------------
Nome da tabela: olist_customers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99441 entries, 0 to 99440
Data columns (total 5 columns):
 #   Column                    Non-Null Count  Dtype 
---  ------                    --------------  ----- 
 0   customer_id               99441 non-null  object
 1   customer_unique_id        99441 non-null  object
 2   customer_zip_code_prefix  99441 non-null  int64 
 3   customer_city             99441 non-null  object
 4   customer_state            99441 non-null  object
dtypes: int64(1), object(4)
memory usage: 3.8+ MB
None
--------------------------------------------------

import sqlite3

con = sqlite3.connect("olist_dataset.db")

for nome_tabela, df in dataframes.items():
    df.to_sql(nome_tabela, con, if_exists='replace', index=False)

#Carrego a extensao sql primeiro e em seguia faço a conexão com o banco de dados
%load_ext sql 
%sql sqlite:///olist_dataset.db

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%%sql
SELECT * FROM olist_customers_dataset LIMIT 5

 * sqlite:///olist_dataset.db
Done.

pd.read_sql("Select * From olist_customers_dataset;", con).head()

%%sql
SELECT * FROM olist_orders_dataset limit 1

 * sqlite:///olist_dataset.db
Done.

%%sql
SELECT
    strftime('%m', order_approved_at) as mes,
    count(*) as "total de vendas"
FROM olist_orders_dataset
GROUP BY mes

 * sqlite:///olist_dataset.db
Done.

%%sql
SELECT
    (count(*))/ 12 as "media de vendas mensais"
FROM olist_orders_dataset

 * sqlite:///olist_dataset.db
Done.

%%sql

WITH vendas_por_mes as(
    SELECT
        strftime('%m', order_approved_at) as mes,
        count(*) as total_vendas
    FROM
        olist_orders_dataset
    GROUP BY
        mes
),
media_geral as (
    SELECT
        avg(total_vendas) as media_mensal
    FROM
        vendas_por_mes
)
SELECT 
    v.mes,
    v.total_vendas,
    Round(v.total_vendas / m.media_mensal, 2) as ind_sazonal 
FROM
    vendas_por_mes as v,
    media_geral as m
ORDER BY
    v.mes

 * sqlite:///olist_dataset.db
Done.

dataframes['olist_orders_dataset'].head(1)

dfa = dataframes['olist_orders_dataset']
dfa['mes'] = dfa['order_approved_at'].dt.month
vendas_p_mes = dfa.groupby('mes').size().reset_index(name='total_vendas')
media_mensal = (dfa['order_approved_at'].count()) / 12
vendas_p_mes['indice_sazonal'] = round(vendas_p_mes['total_vendas'] / media_mensal, 2)
vendas_p_mes = vendas_p_mes.sort_values('mes').reset_index(drop=True)

vendas_p_mes

# Grafico de vendas mensais. 
plt.figure(figsize=(10, 5))
plt.bar(vendas_p_mes['mes'], vendas_p_mes['total_vendas'], color='skyblue')
plt.axhline(y=media_mensal, color='r', linestyle='--', label=f'Média Mensal: {media_mensal:.0f}')
plt.title('Vendas Mensais')
plt.xlabel('Mês')
plt.ylabel('Total de Vendas')
plt.xticks(range(1, 13))
plt.legend()
plt.grid(axis='y', alpha=0.3)
plt.show()

# Grafico de indice sazonal
plt.figure(figsize=(10, 5))
plt.plot(vendas_p_mes['mes'], vendas_p_mes['indice_sazonal'], marker='o', color='green', label='Índice Sazonal')
plt.axhline(y=1, color='gray', linestyle='--', label='Média (1.0)')
plt.title('Sazonalidade das Vendas (Índice)')
plt.xlabel('Mês')
plt.ylabel('Índice Sazonal')
plt.xticks(range(1, 13))
plt.legend()
plt.grid(alpha=0.3)
plt.show()

%%sql

SELECT
    julianday(order_delivered_customer_date) - julianday(order_approved_at) AS tempo_entrega_dias
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date IS NOT NULL
    AND order_approved_at IS NOT NULL
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
limit 10

 * sqlite:///olist_dataset.db
Done.

%%sql 

SELECT
    CAST(julianday(order_delivered_customer_date) - julianday(order_approved_at) as INTEGER) as dias_ate_entrega,
    COUNT(*) as total_pedidos
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date is not null
    AND order_approved_at is not null
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
GROUP BY
    dias_ate_entrega
ORDER BY
    dias_ate_entrega 
LIMIT 30

 * sqlite:///olist_dataset.db
Done.

%%sql

SELECT
    CAST(julianday(order_delivered_customer_date) - julianday(order_approved_at) as INTEGER) as dias_ate_entrega,
    COUNT(*) as total_pedidos
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date is not null
    AND order_approved_at is not null
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
GROUP BY
    dias_ate_entrega
ORDER BY
    total_pedidos desc
LIMIT 30

 * sqlite:///olist_dataset.db
Done.

%%sql

SELECT
    CAST(julianday(order_delivered_customer_date) - julianday(order_approved_at) as INTEGER) as dias_ate_entrega,
    COUNT(*) as total_pedidos
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date is not null
    AND order_approved_at is not null
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
GROUP BY
    dias_ate_entrega
ORDER BY
    total_pedidos asc
LIMIT 30

 * sqlite:///olist_dataset.db
Done.

dfb = dataframes['olist_orders_dataset']
dfb_nn = dfb[dfb['order_delivered_customer_date'].notna() & dfb['order_approved_at'].notna()]
dfb_nn['dias_ate_entrega'] = (dfb_nn['order_delivered_customer_date'] - dfb_nn['order_approved_at']).dt.days
dfb_nn = dfb_nn[dfb_nn['dias_ate_entrega'] >= 0]

queryb = (
    dfb_nn
    .groupby('dias_ate_entrega')
    .size()
    .reset_index(name='total_pedidos')
    .sort_values('total_pedidos', ascending=False)
)

queryb.head(30)

/tmp/ipykernel_5750/1770269448.py:3: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

# Histograma
sns.histplot(dfb_nn['dias_ate_entrega'], bins=60, kde=True, color='steelblue')
plt.title('Distribuição de Prazos de Entrega', fontsize=16)
plt.xlabel('Dias desde Aprovação até Entrega', fontsize=12)
plt.ylabel('Número de Pedidos', fontsize=12)
plt.grid(linestyle='--', alpha=0.7)
plt.figure(figsize=(24, 12))

plt.tight_layout()
plt.show()

<Figure size 2400x1200 with 0 Axes>

%%sql

WITH geo_coordenadas AS (
    SELECT
        geolocation_zip_code_prefix,
        AVG(geolocation_lat) AS lat,
        AVG(geolocation_lng) AS lng
    FROM olist_geolocation_dataset
    GROUP BY geolocation_zip_code_prefix
),
cliente_coordenadas AS (
    SELECT
        cli.customer_id,
        geo.lat AS customer_lat,
        geo.lng AS customer_lng
    FROM olist_customers_dataset cli
    JOIN geo_coordenadas geo
    ON cli.customer_zip_code_prefix = geo.geolocation_zip_code_prefix
),
vendedor_coordenadas AS (
    SELECT
        vend.seller_id,
        geo.lat AS seller_lat,
        geo.lng AS seller_lng
    FROM olist_sellers_dataset vend
    JOIN geo_coordenadas geo ON vend.seller_zip_code_prefix = geo.geolocation_zip_code_prefix
),
frete_com_distancia AS (
    SELECT
        oi.order_id,
        oi.freight_value,
        6371 * 2 * ASIN(SQRT(
            POWER(SIN(RADIANS(cc.customer_lat - sc.seller_lat) / 2), 2) +
            COS(RADIANS(sc.seller_lat)) * COS(RADIANS(cc.customer_lat)) *
            POWER(SIN(RADIANS(cc.customer_lng - sc.seller_lng) / 2), 2)
        )) AS distancia_km
    FROM olist_order_items_dataset oi
    JOIN olist_orders_dataset o ON oi.order_id = o.order_id
    JOIN cliente_coordenadas cc ON o.customer_id = cc.customer_id
    JOIN vendedor_coordenadas sc ON oi.seller_id = sc.seller_id
    WHERE oi.freight_value IS NOT NULL
)

SELECT 
    FLOOR(distancia_km / 100) * 100 AS faixa_distancia_km,
    AVG(freight_value) AS media_frete,
    COUNT(*) AS total_pedidos
FROM frete_com_distancia
WHERE distancia_km IS NOT NULL
GROUP BY faixa_distancia_km
ORDER BY faixa_distancia_km

 * sqlite:///olist_dataset.db
Done.

# Vou reoodernar os dataset em nomes pequenos para ficar mais fácil de acessar nas querys seguintes.
pag = dataframes['olist_order_payments_dataset']
items = dataframes['olist_order_items_dataset']
ped = dataframes['olist_orders_dataset']
ctg_nome = dataframes['product_category_name_translation']
prod = dataframes['olist_products_dataset']
vend = dataframes['olist_sellers_dataset']
reviews = dataframes['olist_order_reviews_dataset']
geo = dataframes['olist_geolocation_dataset']
cli = dataframes['olist_customers_dataset']

import pandas as pd
import numpy as np

# Calcula as coordenadas médias por CEP
coordenadas_cep = geo.groupby('geolocation_zip_code_prefix').agg({
    'geolocation_lat': 'mean',
    'geolocation_lng': 'mean'
}).reset_index()
coordenadas_cep.columns = ['cep', 'lat', 'lng']

# Adiciona coordenadas aos clientes
clientes_coord = pd.merge(
    cli,
    coordenadas_cep,
    left_on='customer_zip_code_prefix',
    right_on='cep',
    how='left'
)[['customer_id', 'lat', 'lng']].rename(columns={
    'lat': 'cliente_lat',
    'lng': 'cliente_lng'
})

# Adiciona coordenadas aos vendedores
vendedores_coord = pd.merge(
    vend,
    coordenadas_cep,
    left_on='seller_zip_code_prefix',
    right_on='cep',
    how='left'
)[['seller_id', 'lat', 'lng']].rename(columns={
    'lat': 'vendedor_lat',
    'lng': 'vendedor_lng'
})

# Junta todos os dados
dados = (items.merge(ped, on='order_id')
              .merge(clientes_coord, on='customer_id')
              .merge(vendedores_coord, on='seller_id')
              .dropna(subset=['freight_value']))

# Calcula distância usando fórmula haversine
def calcular_distancia(row):
    lat1, lon1 = row['vendedor_lat'], row['vendedor_lng']
    lat2, lon2 = row['cliente_lat'], row['cliente_lng']
    
    lat1, lon1, lat2, lon2 = map(np.radians, [lat1, lon1, lat2, lon2])
    dlat, dlon = lat2 - lat1, lon2 - lon1
    
    a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2
    return 6371 * 2 * np.arcsin(np.sqrt(a))

dados['distancia_km'] = dados.apply(calcular_distancia, axis=1).dropna()

# Agrupa por faixas de 100km e calcula estatísticas
resultado = (dados.assign(faixa_distancia=lambda x: (x['distancia_km'] // 100) * 100)
                 .groupby('faixa_distancia')
                 .agg(frete_medio=('freight_value', 'mean'),
                      total_pedidos=('order_id', 'count'))
                 .sort_index())

resultado

plt.figure(figsize=(10, 5))
sns.regplot(x=resultado.index, y=resultado['frete_medio'], 
            scatter_kws={'s': 100}, line_kws={'color': 'red'})
plt.xlabel('Distância (faixas de 100 km)')
plt.ylabel('Frete Médio (R$)')
plt.title('Relação entre Distância e Frete Médio')
plt.grid(True)
plt.show()

%%sql

SELECT
    products.product_category_name as categoria,
    round(sum(items.price), 2) as faturamento
FROM
    olist_order_items_dataset as items
    left join
    olist_products_dataset as products
    on items.product_id = products.product_id
GROUP BY 
    categoria
ORDER BY
    faturamento DESC
limit 10

 * sqlite:///olist_dataset.db
Done.

merged = items.merge(prod, on='product_id', how='left')

# Agrupar por categoria e somar o faturamento
faturamento_por_categoria = (
    merged.groupby('product_category_name')['price']
    .sum()
    .round(2)
    .sort_values(ascending=False)
    .head(10)
    .reset_index()
    .rename(columns={'product_category_name': 'categoria', 'price': 'faturamento'})
)

# Grafico de faturamento com base nas categorias
sns.set(style="darkgrid")
plt.figure(figsize=(12, 6))
sns.barplot(
    data=faturamento_por_categoria,
    x='faturamento',
    y='categoria',
    palette='crest',
    hue='categoria'
)

plt.title('Top 10 Categorias com Maior Faturamento', fontsize=14)
plt.xlabel('Faturamento (R$)', fontsize=12)
plt.ylabel('Categoria', fontsize=12)
plt.tight_layout()
plt.show()

%%sql

SELECT
    c.customer_state AS estado,
    ROUND(SUM(oi.price) / COUNT(DISTINCT o.order_id), 2) AS valor_medio_pedido
FROM
    olist_orders_dataset AS o
JOIN
    olist_customers_dataset AS c
    ON o.customer_id = c.customer_id
JOIN
    olist_order_items_dataset AS oi
    ON o.order_id = oi.order_id
GROUP BY
    estado
ORDER BY
    valor_medio_pedido DESC

 * sqlite:///olist_dataset.db
Done.

%%sql
SELECT
    cli.customer_state AS estado,
    COUNT(*) AS numero_de_pedidos
FROM
    olist_customers_dataset AS cli
LEFT JOIN
    olist_orders_dataset AS ped
    ON cli.customer_id = ped.customer_id
GROUP BY
    estado
ORDER BY
    numero_de_pedidos DESC

limit 5

 * sqlite:///olist_dataset.db
Done.

# Merge dos dataframes
merged = ped.merge(cli, on='customer_id') \
               .merge(items, on='order_id')

# Agrupar por estado e order_id 
pedido_por_estado = (
    merged.groupby(['customer_state', 'order_id'])['price'].sum().reset_index()
)

# agrupar por estado para calcular a média dos valores dos pedidos
resultado = (
    pedido_por_estado.groupby('customer_state')['price']
    .mean()
    .round(2)
    .reset_index()
    .rename(columns={'customer_state': 'estado', 'price': 'valor_medio_pedido'})
    .sort_values(by='valor_medio_pedido', ascending=False)
)

resultado

# Merge
merged = ped.merge(cli, on='customer_id') \
               .merge(items, on='order_id')

# Agrupar por estado e order_id para somar os preços por pedido
pedido_por_estado = (
    merged.groupby(['customer_state', 'order_id'])['price'].sum().reset_index()
)

# Agora agrupar por estado para calcular a média dos valores dos pedidos
resultado = (
    pedido_por_estado.groupby('customer_state')['price']
    .mean()
    .round(2)
    .reset_index()
    .rename(columns={'customer_state': 'estado', 'price': 'valor_medio_pedido'})
    .sort_values(by='valor_medio_pedido', ascending=False)
)

# gráfico de barras
plt.figure(figsize=(12,6))
plt.bar(resultado['estado'], resultado['valor_medio_pedido'], color='skyblue')
plt.xlabel('Estado')
plt.ylabel('Valor Médio do Pedido')
plt.title('Valor Médio do Pedido por Estado')
plt.xticks(rotation=45)
plt.grid(axis='y', linestyle='--', alpha=0.7)
plt.tight_layout()
plt.show()

print("VERIFICAÇÃO INICIAL:")
print(f"Total de pedidos: {len(ped)}")
print(f"Clientes únicos (customer_id): {ped['customer_id'].nunique()}")
print(f"Pedidos por cliente (média): {len(ped)/ped['customer_id'].nunique():.2f}")

# Juntar com informações de clientes únicos
merged_df = ped.merge(cli[['customer_id', 'customer_unique_id']], on='customer_id')

# Análise de retenção por cliente único
print("\n=== ANÁLISE DE RETENÇÃO ===")
ped_clientes = merged_df.groupby('customer_unique_id')['order_id'].nunique().reset_index()
ped_clientes.columns = ['customer_unique_id', 'order_count']

ped_clientes['is_recurrent'] = ped_clientes['order_count'] > 1

total_clientes = len(ped_clientes)
cliente_recorrente = ped_clientes['is_recurrent'].sum()
taxa_retencao = cliente_recorrente / total_clientes * 100

print(f"Taxa de retenção: {taxa_retencao:.2f}%")
print(f"Total de clientes únicos: {total_clientes}")
print(f"Clientes recorrentes: {cliente_recorrente}")
print(f"Clientes com apenas 1 pedido: {total_clientes - cliente_recorrente}")

# Distribuição de pedidos por cliente
print("\n=== DISTRIBUIÇÃO DE PEDIDOS ===")
distribuicao_ped = ped_clientes['order_count'].value_counts().sort_index()
print(distribuicao_ped)

VERIFICAÇÃO INICIAL:
Total de pedidos: 99281
Clientes únicos (customer_id): 99281
Pedidos por cliente (média): 1.00

=== ANÁLISE DE RETENÇÃO ===
Taxa de retenção: 3.07%
Total de clientes únicos: 95997
Clientes recorrentes: 2948
Clientes com apenas 1 pedido: 93049

=== DISTRIBUIÇÃO DE PEDIDOS ===
order_count
1     93049
2      2708
3       191
4        30
5         8
6         6
7         3
9         1
17        1
Name: count, dtype: int64

df_previsao = dataframes['olist_orders_dataset']

print(df_previsao.isnull().sum())

order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                   0
order_delivered_carrier_date     1637
order_delivered_customer_date    2819
order_estimated_delivery_date       0
enviados                            0
entregues                           0
mes                                 0
dtype: int64

df_previsao = df_previsao.dropna(subset=['order_delivered_carrier_date', 'order_delivered_customer_date'])

df_previsao.isnull().sum()

order_id                         0
customer_id                      0
order_status                     0
order_purchase_timestamp         0
order_approved_at                0
order_delivered_carrier_date     0
order_delivered_customer_date    0
order_estimated_delivery_date    0
enviados                         0
entregues                        0
mes                              0
dtype: int64

df_previsao['Atrasado'] = (df_previsao['order_delivered_carrier_date'] > df_previsao['order_estimated_delivery_date']).astype(int)

/tmp/ipykernel_5750/4015680055.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

print(df_previsao['Atrasado'].value_counts())
print(f'\nProporção:\n{df_previsao['Atrasado'].value_counts(normalize=True)}')

Atrasado
0    95996
1      465
Name: count, dtype: int64

Proporção:
Atrasado
0    0.995179
1    0.004821
Name: proportion, dtype: float64

df_previsao['tempo_entrega'] = (df_previsao['order_delivered_customer_date'] - df_previsao['order_purchase_timestamp']).dt.days
df_previsao['tempos_estimado'] = (df_previsao['order_estimated_delivery_date'] - df_previsao['order_purchase_timestamp']).dt.days

/tmp/ipykernel_5750/4036768297.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/tmp/ipykernel_5750/4036768297.py:2: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

df_previsao['estimativa_rapida'] = (df_previsao['tempos_estimado'] < 7).astype(int)

/tmp/ipykernel_5750/545958902.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

df_previsao = df_previsao.merge(items[['order_id', 'product_id']], on='order_id', how='left')
df_previsao = df_previsao.merge(prod[['product_id', 'product_category_name']], on='product_id', how='left')

# Todas as colunas do dataset de previsao
df_previsao.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 110180 entries, 0 to 110179
Data columns (total 17 columns):
 #   Column                         Non-Null Count   Dtype         
---  ------                         --------------   -----         
 0   order_id                       110180 non-null  object        
 1   customer_id                    110180 non-null  object        
 2   order_status                   110180 non-null  object        
 3   order_purchase_timestamp       110180 non-null  datetime64[ns]
 4   order_approved_at              110180 non-null  datetime64[ns]
 5   order_delivered_carrier_date   110180 non-null  datetime64[ns]
 6   order_delivered_customer_date  110180 non-null  datetime64[ns]
 7   order_estimated_delivery_date  110180 non-null  datetime64[ns]
 8   enviados                       110180 non-null  int64         
 9   entregues                      110180 non-null  int64         
 10  mes                            110180 non-null  int32         
 11  Atrasado                       110180 non-null  int64         
 12  tempo_entrega                  110180 non-null  int64         
 13  tempos_estimado                110180 non-null  int64         
 14  estimativa_rapida              110180 non-null  int64         
 15  product_id                     110180 non-null  object        
 16  product_category_name          108643 non-null  object        
dtypes: datetime64[ns](5), int32(1), int64(6), object(5)
memory usage: 13.9+ MB

df_previsao.isnull().sum()

order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                   0
order_delivered_carrier_date        0
order_delivered_customer_date       0
order_estimated_delivery_date       0
enviados                            0
entregues                           0
mes                                 0
Atrasado                            0
tempo_entrega                       0
tempos_estimado                     0
estimativa_rapida                   0
product_id                          0
product_category_name            1537
dtype: int64

df_previsao['product_category_name'] = df_previsao['product_category_name'].fillna('desconhecido')

df_previsao.isnull().sum()

order_id                         0
customer_id                      0
order_status                     0
order_purchase_timestamp         0
order_approved_at                0
order_delivered_carrier_date     0
order_delivered_customer_date    0
order_estimated_delivery_date    0
enviados                         0
entregues                        0
mes                              0
Atrasado                         0
tempo_entrega                    0
tempos_estimado                  0
estimativa_rapida                0
product_id                       0
product_category_name            0
dtype: int64

# Enconding pela categoria do produto
le = LabelEncoder()
df_previsao['product_category_encoded'] = le.fit_transform(df_previsao['product_category_name'])

# ultimas features
features = ['tempo_entrega', 'tempos_estimado', 'estimativa_rapida', 'product_category_encoded']
X = df_previsao[features]
y = df_previsao['Atrasado']

# Divisão treino/teste
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42, stratify=y)

model = RandomForestClassifier(random_state=42)
model.fit(X_train, y_train)

# Previsoes
y_pred = model.predict(X_test)

print("Acuracia:", accuracy_score(y_test, y_pred))
print("Precisao:", precision_score(y_test, y_pred))
print("Recall:", recall_score(y_test, y_pred))
print("F1 Score:", f1_score(y_test, y_pred))

Acuracia: 0.994826647304411
Precisao: 0.44776119402985076
Recall: 0.2803738317757009
F1 Score: 0.3448275862068966

sns.boxplot(data=df_previsao, x='Atrasado', y='tempo_entrega')
plt.title('Tempo de Entrega por Status de Atraso')
plt.show()

#clustering
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
sns.set(style="whitegrid")

# Merge pedido e cliente
df3 = pd.merge(ped, cli, on='customer_id')

# df3 com items
df3 = pd.merge(df3, items, on='order_id')

# df3 com pagamentos
df3 = pd.merge(df3, pag, on='order_id')

# df3 com reviews
df3 = pd.merge(df3, reviews[['order_id', 'review_score']], on='order_id', how='left')

# Criacao de coluna
df3['tempo_entrega'] = (df3['order_delivered_customer_date'] - df3['order_purchase_timestamp']).dt.days

df3.head(1)

df_clientes = df3.groupby('customer_id').agg({
    'order_id': 'nunique',                                
    'payment_value': 'sum',
    'price': 'mean',                                      
    'review_score': 'mean',                               
    'tempo_entrega': 'mean'                              
}).reset_index()

df_clientes.columns = ['customer_id', 'qtd_pedidos', 'valor_total', 'ticket_medio', 'avaliacao_media', 'tempo_medio_entrega']

print(df_clientes.info())

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 98651 entries, 0 to 98650
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   customer_id          98651 non-null  object 
 1   qtd_pedidos          98651 non-null  int64  
 2   valor_total          98651 non-null  float64
 3   ticket_medio         98651 non-null  float64
 4   avaliacao_media      97902 non-null  float64
 5   tempo_medio_entrega  96461 non-null  float64
dtypes: float64(4), int64(1), object(1)
memory usage: 4.5+ MB
None

print(df_clientes.isnull().sum())

customer_id               0
qtd_pedidos               0
valor_total               0
ticket_medio              0
avaliacao_media         749
tempo_medio_entrega    2190
dtype: int64

df_clientes = df_clientes.dropna()

# Normalizacao dos dados
features = ['qtd_pedidos', 'valor_total', 'ticket_medio', 'avaliacao_media', 'tempo_medio_entrega']
scaler = StandardScaler()
X_scaled = scaler.fit_transform(df_clientes[features])

inertia = []
for k in range(1, 11):
    kmeans = KMeans(n_clusters=k, random_state=42)
    kmeans.fit(X_scaled)
    inertia.append(kmeans.inertia_)

plt.plot(range(1, 11), inertia, marker='o')
plt.xlabel('Número de Clusters')
plt.ylabel('Inércia')
plt.title('Método do Cotovelo')
plt.show()

kmeans = KMeans(n_clusters=4, random_state=42)
df_clientes['cluster'] = kmeans.fit_predict(X_scaled)

cluster_summary = df_clientes.groupby('cluster')[features].mean().round(2)
display(cluster_summary)

# Vou fazer um merge dos dados novamente, utilizando os dataset anteriomente definidos
df4 = pd.merge(reviews, ped, on='order_id', how='left')
df4 = pd.merge(df4, pag, on='order_id', how='left')
df4 = pd.merge(df4, items, on='order_id', how='left')
df4 = pd.merge(df4, prod, on='product_id', how='left')
df4 = pd.merge(df4, ctg_nome, on='product_category_name', how='left')
df4 = pd.merge(df4, vend, on='seller_id', how='left')

# Criacao de colunas para tempos de entrega
df4['tempo_entrega'] = (df4['order_delivered_customer_date'] - df4['order_purchase_timestamp']).dt.days
df4['estimativa_entrega'] = (df4['order_estimated_delivery_date'] - df4['order_purchase_timestamp']).dt.days
df4['atraso_entrega'] = df4['tempo_entrega'] - df4['estimativa_entrega']

# VAlor total preco e frete
df4['valor_total_pedido'] = df4['price'] + df4['freight_value']

# Avaliação média por categoria de produto (em inglês)
review_categoria = df4.groupby('product_category_name_english')['review_score'].mean().sort_values(ascending=False)

# Top 10 melhores categorias
review_categoria.head(10)

product_category_name_english
cds_dvds_musicals                        4.642857
fashion_childrens_clothes                4.500000
books_general_interest                   4.438503
books_imported                           4.419355
flowers                                  4.419355
costruction_tools_tools                  4.415842
books_technical                          4.375465
food_drink                               4.324138
small_appliances_home_oven_and_coffee    4.320513
luggage_accessories                      4.295945
Name: review_score, dtype: float64

# Top 10 piores categorias
review_categoria.tail(10)

product_category_name_english
furniture_bedroom                    3.841270
audio                                3.840849
furniture_mattress_and_upholstery    3.804878
fashio_female_clothing               3.780000
fixed_telephony                      3.672862
home_comfort_2                       3.642857
fashion_male_clothing                3.548611
office_furniture                     3.526791
diapers_and_hygiene                  3.256410
security_and_services                2.500000
Name: review_score, dtype: float64

plt.figure(figsize=(22, 20))
review_categoria.sort_values().plot(kind='barh')
plt.title('Avaliação Média por Categoria de Produto')
plt.xlabel('Avaliação Média')
plt.ylabel('Categoria de Produto')
plt.show()

# Correlações
print("Correlação entre tempo de entrega e avaliação:", 
      df4['tempo_entrega'].corr(df4['review_score']))
print("Correlação entre atraso na entrega e avaliação:", 
      df4['atraso_entrega'].corr(df4['review_score']))

# Avaliação por faixa de tempo de entrega
df4['faixa_tempo_entrega'] = pd.cut(df4['tempo_entrega'], 
                                   bins=[0, 5, 10, 15, 20, 30, 50, 100],
                                   labels=['0-5', '6-10', '11-15', '16-20', '21-30', '31-50', '50+'])

avaliacao_por_tempo = df4.groupby('faixa_tempo_entrega')['review_score'].mean()

plt.figure(figsize=(10, 5))
sns.lineplot(x=avaliacao_por_tempo.index, y=avaliacao_por_tempo.values)
plt.title('Avaliação Média por Tempo de Entrega (dias)')
plt.xlabel('Tempo de Entrega (dias)')
plt.ylabel('Avaliação Média')
plt.show()

Correlação entre tempo de entrega e avaliação: -0.3027567826448285
Correlação entre atraso na entrega e avaliação: -0.22847841222502543

/tmp/ipykernel_5750/411135487.py:12: FutureWarning:

The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

# Correlação
print("Correlação entre valor total do pedido e avaliação:", 
      df4['valor_total_pedido'].corr(df4['review_score']))

# Avaliação por faixa de valor
df4['faixa_valor_pedido'] = pd.cut(df4['valor_total_pedido'], 
                                  bins=[0, 50, 100, 150, 200, 300, 500, 1000, 5000],
                                  labels=['0-50', '51-100', '101-150', '151-200', '201-300', '301-500', '501-1000', '1000+'])

avaliacao_por_valor = df4.groupby('faixa_valor_pedido')['review_score'].mean()

plt.figure(figsize=(10, 5))
sns.barplot(x=avaliacao_por_valor.index, y=avaliacao_por_valor.values)
plt.title('Avaliação Média por Faixa de Valor do Pedido')
plt.xlabel('Valor do Pedido (R$)')
plt.ylabel('Avaliação Média')
plt.xticks(rotation=45)
plt.show()

Correlação entre valor total do pedido e avaliação: -0.007406649176182689

/tmp/ipykernel_5750/2851571662.py:10: FutureWarning:

The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

# Avaliação por tipo de pagamento
avaliacao_por_pagamento = df4.groupby('payment_type')['review_score'].mean().sort_values(ascending=False)

plt.figure(figsize=(8, 5))
sns.barplot(x=avaliacao_por_pagamento.index, y=avaliacao_por_pagamento.values)
plt.title('Avaliação Média por Método de Pagamento')
plt.xlabel('Método de Pagamento')
plt.ylabel('Avaliação Média')
plt.show()

avaliacao_por_estado = df4.groupby('seller_state')['review_score'].mean().sort_values(ascending=False)

plt.figure(figsize=(12, 6))
sns.barplot(x=avaliacao_por_estado.index, y=avaliacao_por_estado.values)
plt.title('Avaliação Média por Estado do Vendedor')
plt.xlabel('Estado do Vendedor')
plt.ylabel('Avaliação Média')
plt.show()

from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_squared_error

# Preparar dados para o modelo
modelo_df = df4[['review_score', 'tempo_entrega', 'atraso_entrega', 
                'valor_total_pedido', 'payment_installments', 'product_weight_g',
                'product_photos_qty', 'seller_state']].dropna()

# Converter variáveis categóricas
modelo_df = pd.get_dummies(modelo_df, columns=['seller_state'], drop_first=True)

# Separar variáveis
X = modelo_df.drop('review_score', axis=1)
y = modelo_df['review_score']

# Dividir em treino e teste
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Treinar modelo
modelo = RandomForestRegressor(n_estimators=100, random_state=42)
modelo.fit(X_train, y_train)

# Avaliar modelo
y_pred = modelo.predict(X_test)
print("RMSE:", np.sqrt(mean_squared_error(y_test, y_pred)))

# Importância das variáveis
importancia = pd.DataFrame({
    'variavel': X.columns,
    'importancia': modelo.feature_importances_
}).sort_values('importancia', ascending=False)

plt.figure(figsize=(10, 6))
sns.barplot(x='importancia', y='variavel', data=importancia)
plt.title('Importância dos Fatores na Avaliação do Cliente')
plt.xlabel('Importância')
plt.ylabel('Fator')
plt.show()

RMSE: 1.1078294292935758

import plotly.express as px
from ipywidgets import interact, widgets, Layout
from IPython.display import display, HTML
from plotly.offline import init_notebook_mode
import plotly.express as px
import plotly.graph_objects as go


init_notebook_mode(connected=True)

#  Preparação dos dados 
orders_with_items = pd.merge(
    dataframes['olist_orders_dataset'],
    dataframes['olist_order_items_dataset'],
    on='order_id',
    how='inner'
)

orders_with_products = pd.merge(
    orders_with_items,
    dataframes['olist_products_dataset'],
    on='product_id',
    how='left'
)

orders_with_products = pd.merge(
    orders_with_products,
    dataframes['product_category_name_translation'],
    on='product_category_name',
    how='left'
)

orders_with_customers = pd.merge(
    orders_with_products,
    dataframes['olist_customers_dataset'][['customer_id', 'customer_state']],
    on='customer_id',
    how='left'
)

orders_with_sellers = pd.merge(
    orders_with_customers,
    dataframes['olist_sellers_dataset'][['seller_id', 'seller_state']],
    on='seller_id',
    how='left'
)

orders_with_sellers['order_purchase_timestamp'] = pd.to_datetime(orders_with_sellers['order_purchase_timestamp'])
orders_with_sellers['order_date'] = orders_with_sellers['order_purchase_timestamp'].dt.date
orders_with_sellers['order_month'] = orders_with_sellers['order_purchase_timestamp'].dt.to_period('M').astype(str)
orders_with_sellers['product_category_name_english'] = orders_with_sellers['product_category_name_english'].fillna('Unknown')

# 2. Criar controles interativos com layout ajustado
state_dropdown = widgets.Dropdown(
    options=['Todos'] + sorted(orders_with_sellers['customer_state'].unique().tolist()),
    value='Todos',
    description='Estado:',
    layout=Layout(width='50%')
)

category_dropdown = widgets.Dropdown(
    options=['Todos'] + sorted(orders_with_sellers['product_category_name_english'].dropna().unique().tolist()),
    value='Todos',
    description='Categoria:',
    layout=Layout(width='50%')
)

time_period = widgets.RadioButtons(
    options=['Diário', 'Mensal'],
    value='Mensal',
    description='Período:',
    layout=Layout(width='50%')
)

# Container para os controles
controls = widgets.VBox([state_dropdown, category_dropdown, time_period],
                      layout=Layout(border='1px solid gray', padding='10px'))

# Área para o gráfico
output = widgets.Output()

# Função de atualização
def update_plot(customer_state='Todos', product_category='Todos', time_period='Mensal'):
    with output:
        output.clear_output(wait=True)
        
        # Filtragem dos dados
        filtered = orders_with_sellers.copy()
        
        if customer_state != 'Todos':
            filtered = filtered[filtered['customer_state'] == customer_state]
        
        if product_category != 'Todos':
            filtered = filtered[filtered['product_category_name_english'] == product_category]
        
        time_col = 'order_date' if time_period == 'Diário' else 'order_month'
        
        sales_data = filtered.groupby(time_col).agg(
            total_sales=('price', 'sum'),
            order_count=('order_id', 'nunique'),
            avg_order_value=('price', 'mean')
        ).reset_index()
        
        # Criar grafico
        fig = px.line(
            sales_data,
            x=time_col,
            y='total_sales',
            title=f'Evolução das Vendas {"por Dia" if time_period == "Diário" else "por Mês"}',
            labels={
                'total_sales': 'Valor Total de Vendas (R$)',
                time_col: 'Data' if time_period == 'Diário' else 'Mês'
            }
        )
        
        fig.update_layout(
            height=500,
            xaxis_title='Período',
            yaxis_title='Valor Total de Vendas (R$)',
            hovermode='x unified'
        )
        
        # Mostrar gráfico
        display(fig)
        
        # Metricas
        metrics_html = f"""
        <div style="margin:10px; padding:10px; border:1px solid #ddd; border-radius:5px;">
            <h4>Métricas:</h4>
            <p><b>Total de Vendas:</b> R$ {sales_data['total_sales'].sum():,.2f}</p>
            <p><b>Número de Pedidos:</b> {sales_data['order_count'].sum():,}</p>
            <p><b>Valor Médio por Pedido:</b> R$ {sales_data['avg_order_value'].mean():,.2f}</p>
        </div>
        """
        display(HTML(metrics_html))

# Configurar interatividade
def on_change(change):
    update_plot(
        customer_state=state_dropdown.value,
        product_category=category_dropdown.value,
        time_period=time_period.value
    )

state_dropdown.observe(on_change, names='value')
category_dropdown.observe(on_change, names='value')
time_period.observe(on_change, names='value')

# Exibir o dashboard
display(HTML("<h2>Dashboard de Evolução de Vendas</h2>"))
display(controls)
display(output)

# Atualizar com dados iniciais
update_plot()

VBox(children=(Dropdown(description='Estado:', layout=Layout(width='50%'), options=('Todos', 'AC', 'AL', 'AM',…

Output()

import requests

# GeoJSON dos estados do Brasil
url = 'https://raw.githubusercontent.com/codeforamerica/click_that_hood/master/public/data/brazil-states.geojson'
geojson_states = requests.get(url).json()

# Mapeamento de siglas para nomes completos
uf_map = {
    'AC': 'Acre', 'AL': 'Alagoas', 'AP': 'Amapá', 'AM': 'Amazonas',
    'BA': 'Bahia', 'CE': 'Ceará', 'DF': 'Distrito Federal', 'ES': 'Espírito Santo',
    'GO': 'Goiás', 'MA': 'Maranhão', 'MT': 'Mato Grosso', 'MS': 'Mato Grosso do Sul',
    'MG': 'Minas Gerais', 'PA': 'Pará', 'PB': 'Paraíba', 'PR': 'Paraná',
    'PE': 'Pernambuco', 'PI': 'Piauí', 'RJ': 'Rio de Janeiro', 'RN': 'Rio Grande do Norte',
    'RS': 'Rio Grande do Sul', 'RO': 'Rondônia', 'RR': 'Roraima', 'SC': 'Santa Catarina',
    'SP': 'São Paulo', 'SE': 'Sergipe', 'TO': 'Tocantins'
}

# Preparar dados
ped_cli = ped.merge(cli, on='customer_id')
orders_by_state = ped_cli['customer_state'].value_counts().reset_index()
orders_by_state.columns = ['uf', 'num_orders']
orders_by_state['state_name'] = orders_by_state['uf'].map(uf_map)

# Gerar mapa 
fig = px.choropleth(
    orders_by_state,
    geojson=geojson_states,
    locations='state_name',
    featureidkey='properties.name',
    color='num_orders',
    color_continuous_scale='Blues',
    labels={'num_orders': 'Número de Pedidos'},
)

# visualização
fig.update_geos(
    fitbounds="locations",
    visible=False,
    showcountries=False,
    showcoastlines=False,
    showland=True,
    landcolor='white'
)

fig.update_layout(
    title_text='📦 Concentração de Pedidos por Estado no Brasil',
    title_x=0.5,
    margin={"r":0,"t":50,"l":0,"b":0},
    height=600,
    width=800,
    coloraxis_colorbar=dict(
        title="Pedidos",
        ticks="outside"
    )
)

fig.show()

# Juntar pedidos com clientes
ped_cli = ped.merge(cli, on='customer_id')

# Calcular valor total por pedido
total_ped = items.groupby('order_id')['price'].sum().reset_index()

# Juntar com estado do cliente
total_ped_state = ped_cli.merge(total_ped, on='order_id')

# 2. Calcular valor médio gasto por estado
avg_spend_by_state = total_ped_state.groupby('customer_state')['price'].mean().reset_index()
avg_spend_by_state.columns = ['uf', 'avg_spent']
avg_spend_by_state['state_name'] = avg_spend_by_state['uf'].map(uf_map)

# 3. Criar o gráfico
fig = px.choropleth(
    avg_spend_by_state,
    geojson=geojson_states,
    locations='state_name',
    featureidkey='properties.name',
    color='avg_spent',
    color_continuous_scale='Oranges',
    labels={'avg_spent': 'Ticket Médio (R$)'},
)

# 4. Ajustes visuais
fig.update_geos(
    fitbounds="locations",
    visible=False,
    showcountries=False,
    showcoastlines=False,
    showland=True,
    landcolor='white'
)

fig.update_layout(
    title_text='🛒 Valor Médio Gasto por Pedido em Cada Estado',
    title_x=0.5,
    margin={"r":0,"t":50,"l":0,"b":0},
    height=600,
    width=800,
    coloraxis_colorbar=dict(
        title="R$ por pedido",
        ticks="outside"
    )
)

fig.show()

# Carregar as tabelas necessárias
reviews = dataframes['olist_order_reviews_dataset']
orders = dataframes['olist_orders_dataset']

# Juntar as tabelas pela chave order_id
merged = pd.merge(reviews, orders, on='order_id', how='inner')

# Converter colunas de data
merged['order_purchase_timestamp'] = pd.to_datetime(merged['order_purchase_timestamp'])
merged['order_delivered_customer_date'] = pd.to_datetime(merged['order_delivered_customer_date'])

# Calcular o tempo de entrega (em dias)
merged['delivery_time'] = (merged['order_delivered_customer_date'] - merged['order_purchase_timestamp']).dt.days

# Remover linhas com delivery_time nulo
merged = merged.dropna(subset=['delivery_time'])

# boxplot
plt.figure(figsize=(10, 6))
sns.boxplot(data=merged, x='review_score', y='delivery_time')
plt.title('Tempo de entrega por nota de avaliação')
plt.xlabel('Nota do cliente')
plt.ylabel('Tempo de entrega (dias)')
plt.grid(True)
plt.show()

# grafico de barras
avg_delivery = merged.groupby('review_score')['delivery_time'].mean().reset_index()
plt.figure(figsize=(8, 5))
sns.barplot(data=avg_delivery, x='review_score', y='delivery_time', color=sns.color_palette('PuBu')[4])
plt.title('Média do tempo de entrega por nota de avaliação')
plt.xlabel('Nota do cliente')
plt.ylabel('Tempo médio de entrega (dias)')
plt.grid(True)
plt.show()

# Converter datas
ped['order_purchase_timestamp'] = pd.to_datetime(ped['order_purchase_timestamp'])
ped['order_delivered_customer_date'] = pd.to_datetime(ped['order_delivered_customer_date'])

# Tempo de entrega por pedido
ped['delivery_time'] = (ped['order_delivered_customer_date'] - ped['order_purchase_timestamp']).dt.days

#Juntar reviews com pedidos
review_ped = pd.merge(reviews[['order_id', 'review_score']], ped[['order_id', 'delivery_time']], on='order_id')

# Juntar com os itens
base = pd.merge(review_ped, items[['order_id', 'seller_id']], on='order_id')

# Agrupar por vendedor
metricas_vend = base.groupby('seller_id').agg(
    volume_vendas=('order_id', 'count'),
    nota_media=('review_score', 'mean'),
    entrega_media=('delivery_time', 'mean')
).reset_index()

# Opcional: filtrar vendedores com pelo menos 20 vendas
metricas_vend = metricas_vend[metricas_vend['volume_vendas'] >= 20]

top_volume = metricas_vend.sort_values(by='volume_vendas', ascending=False).head(10)

sns.barplot(data=top_volume, x='volume_vendas', y='seller_id', palette='Blues_r')
plt.title('Top 10 vendedores por volume de vendas')
plt.xlabel('Número de vendas')
plt.ylabel('ID do vendedor')
plt.show()

/tmp/ipykernel_5750/2636866980.py:3: FutureWarning:


Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.

top_nota = metricas_vend.sort_values(by='nota_media', ascending=False).head(10)

sns.barplot(data=top_nota, x='nota_media', y='seller_id', palette='Greens_r')
plt.title('Top 10 vendedores por avaliação média')
plt.xlabel('Nota média')
plt.ylabel('ID do vendedor')
plt.show()

/tmp/ipykernel_5750/1127399185.py:3: FutureWarning:


Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.

top_entrega = metricas_vend.sort_values(by='entrega_media', ascending=True).head(10)

sns.barplot(data=top_entrega, x='entrega_media', y='seller_id', palette='Oranges_r')
plt.title('Top 10 vendedores com menor tempo de entrega')
plt.xlabel('Tempo médio de entrega (dias)')
plt.ylabel('ID do vendedor')
plt.show()

/tmp/ipykernel_5750/1383419301.py:3: FutureWarning:


Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.

from IPython.display import display, Markdown

n_vendedores = metricas_vend.shape[0]
media_volume = metricas_vend['volume_vendas'].mean()
media_nota = metricas_vend['nota_media'].mean()
media_entrega = metricas_vend['entrega_media'].mean()

display(Markdown(f"""
# Análise de Desempenho dos Vendedores

**Total de vendedores analisados:** {n_vendedores}  
**Volume médio de vendas:** {media_volume:.1f}  
**Nota média dos clientes:** {media_nota:.2f}  
**Tempo médio de entrega:** {media_entrega:.2f} dias
"""))

# Ranking ponderado simples (ajustável)
metricas_vend['ranking'] = (
    metricas_vend['volume_vendas'].rank(ascending=False) +
    metricas_vend['nota_media'].rank(ascending=False) +
    metricas_vend['entrega_media'].rank(ascending=True)
)

ranking_geral = metricas_vend.sort_values(by='ranking').head(10)

display(Markdown("# Ranking Geral dos Top 10 Vendedores"))
display(ranking_geral[['seller_id', 'volume_vendas', 'nota_media', 'entrega_media']])

	customer_id	customer_unique_id	customer_zip_code_prefix	customer_city	customer_state
0	06b8999e2fba1a1fbc88172c00ba8bc7	861eff4711a542e4b93843c6dd7febb0	14409	franca	SP
1	18955e83d337fd6b2def6b18a428ac77	290c77bc529b7ac935b93aa66c333dc3	9790	sao bernardo do campo	SP
2	4e7b3e00288586ebd08712fdd0374a03	060e732b5b29e8181a18229c7b0b2b5e	1151	sao paulo	SP
3	b2b6027bc5c5109e529d4dc6358b12c3	259dac757896d24d7702b9acbbff3f3c	8775	mogi das cruzes	SP
4	4f2d8ab171c80ec8364f7c12e35b23ad	345ecd01c38d18a9036ed96c73b8d066	13056	campinas	SP
...	...	...	...	...	...
99436	17ddf5dd5d51696bb3d7c6291687be6f	1a29b476fee25c95fbafc67c5ac95cf8	3937	sao paulo	SP
99437	e7b71a9017aa05c9a7fd292d714858e8	d52a67c98be1cf6a5c84435bd38d095d	6764	taboao da serra	SP
99438	5e28dfe12db7fb50a4b2f691faecea5e	e9f50caf99f032f0bf3c55141f019d99	60115	fortaleza	CE
99439	56b18e2166679b8a959d72dd06da27f9	73c2643a0a458b49f58cea58833b192e	92120	canoas	RS
99440	274fa6071e5e17fe303b9748641082c8	84732c5050c01db9b23e19ba39899398	6703	cotia	SP

	review_id	order_id	review_score	review_comment_title	review_comment_message	review_creation_date	review_answer_timestamp
0	7bc2406110b926393aa56f80a40eba40	73fc7af87114b39712e6da79b0a377eb	4	NaN	NaN	2018-01-18 00:00:00	2018-01-18 21:46:59
1	80e641a11e56f04c1ad469d5645fdfde	a548910a1c6147796b98fdf73dbeba33	5	NaN	NaN	2018-03-10 00:00:00	2018-03-11 03:05:13
2	228ce5500dc1d8e020d8d1322874b6f0	f9e4b658b201a9f2ecdecbb34bed034b	5	NaN	NaN	2018-02-17 00:00:00	2018-02-18 14:36:24
3	e64fb393e7b32834bb789ff8bb30750e	658677c97b385a9be170737859d3511b	5	NaN	Recebi bem antes do prazo estipulado.	2017-04-21 00:00:00	2017-04-21 22:02:06
4	f7c4243c7fe1938f181bec41a392bdeb	8e6bfb81e283fa7e4f11123a3fb894f1	5	NaN	Parabéns lojas lannister adorei comprar pela I...	2018-03-01 00:00:00	2018-03-02 10:26:53
...	...	...	...	...	...	...	...
99219	574ed12dd733e5fa530cfd4bbf39d7c9	2a8c23fee101d4d5662fa670396eb8da	5	NaN	NaN	2018-07-07 00:00:00	2018-07-14 17:18:30
99220	f3897127253a9592a73be9bdfdf4ed7a	22ec9f0669f784db00fa86d035cf8602	5	NaN	NaN	2017-12-09 00:00:00	2017-12-11 20:06:42
99221	b3de70c89b1510c4cd3d0649fd302472	55d4004744368f5571d1f590031933e4	5	NaN	Excelente mochila, entrega super rápida. Super...	2018-03-22 00:00:00	2018-03-23 09:10:43
99222	1adeb9d84d72fe4e337617733eb85149	7725825d039fc1f0ceb7635e3f7d9206	4	NaN	NaN	2018-07-01 00:00:00	2018-07-02 12:59:13
99223	efe49f1d6f951dd88b51e6ccd4cc548f	90531360ecb1eec2a1fbb265a0db0508	1	NaN	meu produto chegou e ja tenho que devolver, po...	2017-07-03 00:00:00	2017-07-03 21:01:49

	geolocation_zip_code_prefix	geolocation_lat	geolocation_lng	geolocation_city	geolocation_state
519	1001	-23.551337	-46.634027	sao paulo	SP
583	1001	-23.551337	-46.634027	sao paulo	SP
818	1001	-23.551337	-46.634027	sao paulo	SP
206	1001	-23.550498	-46.634338	sao paulo	SP
429	1001	-23.550498	-46.634338	sao paulo	SP
...	...	...	...	...	...
999899	99980	-28.387432	-51.847727	david canabarro	RS
999958	99980	-28.387059	-51.848964	david canabarro	RS
1000129	99980	-28.387059	-51.848964	david canabarro	RS
999775	99980	-28.386689	-51.847091	david canabarro	RS
1000133	99980	-28.386689	-51.847091	david canabarro	RS

	order_id	payment_sequential	payment_type	payment_installments	payment_value
0	b81ef226f3fe1789b1e8b2acac839d17	1	credit_card	8	99.33
1	a9810da82917af2d9aefd1278f1dcfa0	1	credit_card	1	24.39
2	25e8ea4e93396b6fa0d3dd708e76c1bd	1	credit_card	1	65.71
3	ba78997921bbcdc1373bb41e913ab953	1	credit_card	8	107.78
4	42fdf880ba16b47b59251dd489d4441a	1	credit_card	2	128.45

	order_id	order_item_id	product_id	seller_id	shipping_limit_date	price	freight_value
0	00010242fe8c5a6d1ba2dd792cb16214	1	4244733e06e7ecb4970a6e2683c13e61	48436dade18ac8b2bce089ec2a041202	2017-09-19 09:45:35	58.90	13.29
1	00018f77f2f0320c557190d7a144bdd3	1	e5f2d52b802189ee658865ca93d83a8f	dd7ddc04e1b6c2c614352b383efe2d36	2017-05-03 11:05:13	239.90	19.93
2	000229ec398224ef6ca0657da4fc703e	1	c777355d18b72b67abbeef9df44fd0fd	5b51032eddd242adc84c38acab88f23d	2018-01-18 14:48:30	199.00	17.87
3	00024acbcdf0a6daa1e931b038114c75	1	7634da152a4610f1595efa32f14722fc	9d7a1d34a5052409006425275ba1c2b4	2018-08-15 10:10:18	12.99	12.79
4	00042b26cf59d7ce69dfabb4e55b4fd9	1	ac6c3623068f30de03045865e4e10089	df560393f3a51e74553ab94004ba5c87	2017-02-13 13:57:51	199.90	18.14

	order_id	customer_id	order_status	order_purchase_timestamp	order_approved_at	order_delivered_carrier_date	order_delivered_customer_date	order_estimated_delivery_date	enviados	entregues
0	e481f51cbdc54678b7cc49136f2d6af7	9ef432eb6251297304e76186b10a928d	delivered	2017-10-02 10:56:33	2017-10-02 11:07:15	2017-10-04 19:55:00	2017-10-10 21:25:13	2017-10-18 00:00:00	1	1
1	53cdb2fc8bc7dce0b6741e2150273451	b0830fb4747a6c6d20dea0b8c802d7ef	delivered	2018-07-24 20:41:37	2018-07-26 03:24:27	2018-07-26 14:31:00	2018-08-07 15:27:45	2018-08-13 00:00:00	1	1
2	47770eb9100c2d0c44946d9cf07ec65d	41ce2a54c0b03bf3443c3d931a367089	delivered	2018-08-08 08:38:49	2018-08-08 08:55:23	2018-08-08 13:50:00	2018-08-17 18:06:29	2018-09-04 00:00:00	1	1
3	949d5b44dbf5de918fe9c16f97b45f8a	f88197465ea7920adcdbec7375364d82	delivered	2017-11-18 19:28:06	2017-11-18 19:45:59	2017-11-22 13:39:59	2017-12-02 00:28:42	2017-12-15 00:00:00	1	1
4	ad21c59c0840e6cb83a9ceb5573f8159	8ab97904e6daea8866dbdbc4fb7aad2c	delivered	2018-02-13 21:18:39	2018-02-13 22:20:29	2018-02-14 19:46:34	2018-02-16 18:17:02	2018-02-26 00:00:00	1	1

	product_category_name	product_category_name_english
0	beleza_saude	health_beauty
1	informatica_acessorios	computers_accessories
2	automotivo	auto
3	cama_mesa_banho	bed_bath_table
4	moveis_decoracao	furniture_decor

	product_id	product_category_name	product_name_lenght	product_description_lenght	product_photos_qty	product_weight_g	product_length_cm	product_height_cm	product_width_cm
0	1e9e8ef04dbcff4541ed26657ea517e5	perfumaria	40.0	287.0	1.0	225.0	16.0	10.0	14.0
1	3aa071139cb16b67ca9e5dea641aaa2f	artes	44.0	276.0	1.0	1000.0	30.0	18.0	20.0
2	96bd76ec8810374ed1b65e291975717f	esporte_lazer	46.0	250.0	1.0	154.0	18.0	9.0	15.0
3	cef67bcfe19066a932b7673e239eb23d	bebes	27.0	261.0	1.0	371.0	26.0	4.0	26.0
4	9dc1a7de274444849c219cff195d0b71	utilidades_domesticas	37.0	402.0	4.0	625.0	20.0	17.0	13.0

	seller_id	seller_zip_code_prefix	seller_city	seller_state
0	3442f8959a84dea7ee197c632cb2df15	13023	campinas	SP
1	d1b65fc7debc3361ea86b5f14c68d2e2	13844	mogi guacu	SP
2	ce3ad9de960102d0677a81f5d0bb7b2d	20031	rio de janeiro	RJ
3	c0f3eea2e14555b6faeea3dd58c1b1c3	4195	sao paulo	SP
4	51a04a8a6bdcb23deccc82b0b80742cf	12914	braganca paulista	SP

	geolocation_zip_code_prefix	geolocation_lat	geolocation_lng	geolocation_city	geolocation_state
0	1037	-23.545621	-46.639292	sao paulo	SP
1	1046	-23.546081	-46.644820	sao paulo	SP
2	1046	-23.546129	-46.642951	sao paulo	SP
3	1041	-23.544392	-46.639499	sao paulo	SP
4	1035	-23.541578	-46.641607	sao paulo	SP

mes	total de vendas
01	7947
02	8471
03	9977
04	9152
05	10759
06	9416
07	10150
08	10968
09	4303
10	4910
11	7395
12	5833

mes	total_vendas	ind_sazonal
01	7947	0.96
02	8471	1.02
03	9977	1.21
04	9152	1.11
05	10759	1.3
06	9416	1.14
07	10150	1.23
08	10968	1.33
09	4303	0.52
10	4910	0.59
11	7395	0.89
12	5833	0.71

dias_ate_entrega	total_pedidos
0	158
1	2381
2	3550
3	4321
4	4975
5	6176
6	7580
7	7715
8	6389
9	5869
10	5406
11	4819
12	4471
13	4333
14	3620
15	2887
16	2464
17	2247
18	1940
19	1671
20	1685
21	1408
22	1110
23	987
24	858
25	764
26	675
27	634
28	535
29	443

dias_ate_entrega	total_pedidos
87	1
90	1
91	1
92	1
100	1
101	1
105	1
108	1
109	1
110	1
115	1
117	1
118	1
123	1
125	1
126	1
129	1
132	1
133	1
135	1
136	1
140	1
142	1
148	1
165	1
166	1
168	1
171	1
172	1
173	1

faixa_distancia_km	media_frete	total_pedidos
0	11.757457602896341	20992
100	15.086909044520985	7839
200	17.738878697842658	7741
300	19.233853331663326	15968
400	19.908209614404623	11774
500	19.726619595915707	9206
600	21.58151177422254	5563
700	22.12764135099832	5359
800	21.18396746299282	6823
900	22.81165919282511	3122
1000	24.65998511904762	2016
1100	26.913206249999998	1600
1200	27.54624617737003	1308
1300	26.89457425742574	1010
1400	25.958059171597636	1690
1500	29.941724137931036	754
1600	33.175266362252664	657
1700	37.94852631578947	665
1800	38.36524011299435	708
1900	37.70882558139535	860
2000	38.867048681541576	986
2100	34.23311141304348	1472
2200	37.09705521472392	978
2300	31.190757910228108	1359
2400	32.63035168195719	654
2500	42.45066666666666	225
2600	37.395551839464886	299
2700	45.484078947368424	152
2800	49.69102272727273	88
2900	56.03830188679245	53
3000	60.645714285714284	28
3100	42.949866666666665	75
3200	41.318125	16
3300	33.96806451612903	31
3400	42.98	1
3500	56.69	3
3900	34.88	1
4700	20.8	1
4800	58.08	1
4900	29.244999999999997	2
8600	25.6	1

tempo_entrega_dias
8.429143518675119
12.502291666343808
9.382708332967013
13.196331018581986
2.8309375001117587
16.533125000074506
9.981712962966412
8.470405092928559
18.214560185093433
12.644594907294959

	frete_medio	total_pedidos
faixa_distancia
0.0	11.757458	20992
100.0	15.086909	7839
200.0	17.738879	7741
300.0	19.233853	15968
400.0	19.908210	11774
500.0	19.726620	9206
600.0	21.581512	5563
700.0	22.127641	5359
800.0	21.183967	6823
900.0	22.811659	3122
1000.0	24.659985	2016
1100.0	26.913206	1600
1200.0	27.546246	1308
1300.0	26.894574	1010
1400.0	25.958059	1690
1500.0	29.941724	754
1600.0	33.175266	657
1700.0	37.948526	665
1800.0	38.365240	708
1900.0	37.708826	860
2000.0	38.867049	986
2100.0	34.233111	1472
2200.0	37.097055	978
2300.0	31.190758	1359
2400.0	32.630352	654
2500.0	42.450667	225
2600.0	37.395552	299
2700.0	45.484079	152
2800.0	49.691023	88
2900.0	56.038302	53
3000.0	60.645714	28
3100.0	42.949867	75
3200.0	41.318125	16
3300.0	33.968065	31
3400.0	42.980000	1
3500.0	56.690000	3
3900.0	34.880000	1
4700.0	20.800000	1
4800.0	58.080000	1

categoria	faturamento
beleza_saude	1258681.34
relogios_presentes	1205005.68
cama_mesa_banho	1036988.68
esporte_lazer	988048.97
informatica_acessorios	911954.32
moveis_decoracao	729762.49
cool_stuff	635290.85
utilidades_domesticas	632248.66
automotivo	592720.11
ferramentas_jardim	485256.46

estado	valor_medio_pedido
PB	216.67
AP	198.15
AC	197.32
AL	195.41
RO	186.8
PA	184.48
TO	177.86
PI	176.3
MT	173.26
RN	172.27
CE	171.32
SE	170.79
RR	170.21
MS	164.76
MA	161.8
PE	159.46
BA	152.28
AM	152.09
GO	146.78
SC	144.12
RJ	142.94
DF	142.4
RS	138.08
MG	137.34
PR	136.67
ES	135.82
SP	125.75

dias_ate_entrega	total_pedidos
87	1
90	1
91	1
92	1
100	1
101	1
105	1
108	1
109	1
110	1
115	1
117	1
118	1
123	1
125	1
126	1
129	1
132	1
133	1
135	1
136	1
140	1
142	1
148	1
165	1
166	1
168	1
171	1
172	1
173	1

	qtd_pedidos	valor_total	ticket_medio	avaliacao_media	tempo_medio_entrega
cluster
0	1.0	149.15	97.75	4.66	7.59
1	1.0	234.54	108.78	1.24	19.73
2	1.0	1822.02	1016.29	4.16	13.08
3	1.0	182.92	120.58	4.36	20.26

	seller_id	volume_vendas	nota_media	entrega_media
476	289cdb325fb7e7f891c38608bf9e0962	126	4.579365	6.192000
2399	c70c1b0d8ca86052f45a432a38b73958	335	4.301493	7.252252
2526	d13e50eaa47b4cbe9eb81465865d8cfc	69	4.811594	4.573529
2575	d566c37fa119d5e66c4e9052e83ee4ea	71	4.718310	5.352113
2360	c3cfdc648177fdbbbb35635a37472c53	313	4.447284	9.246006
1934	9f505651f4a6abe901a56cdc21508025	427	4.283372	7.754137
1910	9d4db00d65d7760644ac0c14edb5fd86	99	4.515152	7.673469
100	080199a181c46c657dc5aa235411be3b	82	4.609756	7.621951
1351	6edacfd9f9074789dad6d62ba7950b9c	219	4.360731	8.566820
2307	c003204e1ab016dfa150abc119207b24	201	4.422886	9.270000

Preparação dos Dados:¶

1A) Importar os arquivos CSV do dataset¶

O que fiz acima foi:¶

Limpeza dos dados:¶

Temos nulos em 3 tabelas:¶

Olist_orders_dataset¶

Cada coluna armazena a data e hora respectiva ao seu nome¶

olist_products_dataset¶

olist_order_reviews_dataset¶

Vamos observar abaixo a quantidade de duplicatas nas tabelas do dicionario de df¶

Temos exatos dados duplicatos na tabela.¶

Farei a normalização das colunas¶

Resultado do Processo de formalização das colunas:¶

Fazer um modelo relacional e conectar as tabelas adequadamente¶

Acima importei o sqlite3 e fiz a conexão com um arquivo local .db, que é criado quando executado e se existe será substituido quando o comando ser executado¶

Tipos de Análise que poderão ser feitas:¶

1. ipython-sql¶

2. Pandas com SQL¶

Descrever os Passos de preparação dos dados adotados¶

Importação de arquivos:¶

Limpeza dos dados:¶

Nulos:¶

Duplicatas:¶

Normalização dos dados nas tabelas:¶

Fazer um modelo relacional e conectar as tabelas adequadamente:¶

Análise Exploratória de Dados¶

Optei por fazer as querys utilizando SQL no Banco de dados criado, e as querys com pandas no dicionário de dataframes, pois ficaria redundante escrever o mesmo código sql no pandas para obter o mesmo resultado¶

a) Qual o volume de pedidos por mês? Existe sazonalidade nas vendas?¶

SQL¶

Pandas¶

O código em pandas ficou mais enxuto, porque eu ja tinha feito a query em SQL, então basicamente só fui reescrevendo a query no formato pandas, mas vou explicar o que cada linha faz abaixo.¶

Insights:¶

b) Qual a distribuição do tempo de entrega dos pedidos?¶

SQL¶

Vou limitar as primeiras 30 ocorrências ordenando por os dias com mais pedidos¶

Pandas¶

Insight b¶

c) Qual a relação entre o valor do frete e a distância de entrega?¶

Explicar a query¶

Pandas¶

Insight c)¶

d) Quais são as categorias de produtos mais vendidas em termos de faturamento?¶

pandas¶

Insight D¶

e) Quais estados brasileiros possuem o maior valor médio de pedido?¶

SQL¶

Quero fazer um adendo aqui porque fiz uma query anterior e achei interessante¶

pandas¶

Insights e)¶

3. Solução de Problemas de Negócio (25 pontos)¶

1. Análise de Retenção: Calcule a taxa de clientes recorrentes. Considere um cliente recorrente aquele que fez mais de um pedido no período analisado. Quais insights podemos extrair destes dados?¶

Insight problema de negócio¶

2. Predição de Atraso: Crie um modelo simples para prever se um pedido será entregue com atraso.¶

Vou quebrar esse problema em alguns blocos de código¶

Definir o atraso¶

Features do modelo¶

Preparação dos Dados¶

Vou treinar o Modelo¶

Resultados¶

3.3 Segmentação de Clientes: Utilize técnicas de clustering para segmentar os clientes em grupos. Analise o comportamento de cada grupo e sugira estratégias de marketing específicas para cada um.¶

Preparação e agregação dos dados¶

Criação de Features¶

Pré-processamento dos dados¶

Elbow Method¶

Insights¶

4. Análise de Satisfação: Explore a relação entre a nota de avaliação dos clientes e diferentes aspectos como categoria do produto, tempo de entrega, valor do pedido, etc. Identifique fatores que mais impactam na satisfação do cliente.¶

Preparação dos Dados¶

Análise por Categoria de produto¶

Insights¶

Análise por Tempo de Entrega¶

Insight¶

Análise por Valor do Pedido¶

Insight do gráfico¶

4. Análise por Método de Pagamento¶

Insight do gráfico¶

5. Análise por Estado do Vendedor¶

Insigths¶

6. Análise Multivariada (Fatores mais Importantes)¶

Insights¶

4. Dashboard e visualizaçòes¶

dias_ate_entrega	total_pedidos
87	1
90	1
91	1
92	1
100	1
101	1
105	1
108	1
109	1
110	1
115	1
117	1
118	1
123	1
125	1
126	1
129	1
132	1
133	1
135	1
136	1
140	1
142	1
148	1
165	1
166	1
168	1
171	1
172	1
173	1